package org.srtc.infopub.datacenter.storage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map.Entry;
import java.util.Set;

import org.srtc.infopub.tools.TestXML;
import org.w3c.dom.Element;


public class TestCoalTable {

	/**
	 * 装在所有矿各有什么信息。
	 */
	private HashMap<String, ArrayList<String>> coals;
	
	private TestXML tXML;


	public HashMap<String, ArrayList<String>> getCoals() {
		return coals;
	}


	/**
	 * 
	 * @param filename 矿的配置文件，不是信息类配置文件
	 */
	public TestCoalTable(String filename) {
		tXML = new TestXML(filename);
	
		coals = new HashMap<String, ArrayList<String>>();
	}
	
	
	public String createInsertSql(Entry<String, ArrayList<String>> en) {
		String insertSql = "";
		ArrayList<String> values = en.getValue();
		for (int i = 0; i<values.size(); i++) {
			insertSql = insertSql + values.get(i) + " ";
		}
		insertSql = "\"" + en.getKey() + "\", \"" + insertSql + "\"";
		
		return insertSql;
	}
	
	
	public void createCoalTable(String url, String user, String pwd) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url, user, pwd);
			Statement stat = conn.createStatement();
			coalInfos();
			
			String createSql = "create table if not exists `coal_info` (name varchar(8), infos varchar(48))";
			stat.executeUpdate(createSql);
			
			Set<Entry<String, ArrayList<String>>> set = coals.entrySet();
			Iterator<Entry<String, ArrayList<String>>> it = set.iterator();
			
			while (it.hasNext()) {
				String sql = "insert into `coal_info` values (" + createInsertSql(it.next()) + ")";
				stat.executeUpdate(sql);
				System.out.println(sql);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 返回一个coal的所有信息类
	 * @param coal
	 * @return 没有该coal节点则返回null，否则返回所含信息类
	 */
	public ArrayList<String> getCoalInfo(Element coal) {
		if (coal == null) {
			return null;
		}
		
		ArrayList<String> infos = new ArrayList<String>();
		ArrayList<Element> eList = tXML.getElementList(coal.getChildNodes());
		
		for (int i = 0; i<eList.size(); i++) {
			infos.add(eList.get(i).getAttribute("code"));
		}
		
		return infos;
	}
	
	
	/**
	 * 得到所有矿的信息
	 */
	public void coalInfos() {
		Element root = tXML.rootElement();
		ArrayList<Element> nList = tXML.getElementList(root.getChildNodes());
		for (int i = 0; i<nList.size(); i++) {
			Element cl = (Element)nList.get(i);
			coals.put(cl.getAttribute("code"), this.getCoalInfo(cl));
		}
	}
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		TestCoalTable tct = new TestCoalTable("copy-gzmxx.xml");
		tct.coalInfos();
		tct.createCoalTable("jdbc:mysql://127.0.0.1:3306/yl_tt", "root", "root");
	}

}
